import pymssql
from dbutils.persistent_db import PersistentDB

# 学生类，用于封装学生表的数据
class Student:
    def __init__(self, sno, sname, sex, major_class, birth_date, contact_phone):
        self.sno = sno
        self.sname = sname
        self.sex = sex
        self.major_class = major_class
        self.birth_date = birth_date
        self.contact_phone = contact_phone

    def __repr__(self):
        return f"Student({self.sno}, {self.sname}, {self.sex}, {self.major_class}, {self.birth_date}, {self.contact_phone})"

# 课程类，用于封装课程表的数据
class Course:
    def __init__(self, cno, cname, credit, hours, teacher):
        self.cno = cno
        self.cname = cname
        self.credit = credit
        self.hours = hours
        self.teacher = teacher

    def __repr__(self):
        return f"Course({self.cno}, {self.cname}, {self.credit}, {self.hours}, {self.teacher})"

# 学生作业类，用于封装学生作业表的数据
class StudentHomework:
    def __init__(self, cno, sno, score1, score2, score3):
        self.cno = cno
        self.sno = sno
        self.score1 = score1
        self.score2 = score2
        self.score3 = score3

    def __repr__(self):
        return f"StudentHomework({self.cno}, {self.sno}, {self.score1}, {self.score2}, {self.score3})"

# DAO可扩展连接类
class DAOConnection:
    _instance = None

    def __new__(cls, *args, **kwargs):
        if not cls._instance:
            cls._instance = super().__new__(cls)
            print('单例')
        return cls._instance

    def __init__(self, server, user, password, database):
        self.server = server
        self.user = user
        self.password = password
        self.database = database
        self.connection = None
        self.cursor = None
    # 连接
    def connect(self):
        try:
            # self.connection = pymssql.connect(server=self.server, user=self.user, password=self.password, database=self.database)
            POOL = PersistentDB(
                creator=pymssql,  # 使⽤链接数据库的模块
                maxusage=None,  # ⼀个链接最多被重复使⽤的次数，None表示⽆限制
                setsession=[],  # 开始会话前执⾏的命令列表。
                ping=0,
                # ping MySQL服务端，检查是否服务可⽤。
                closeable=False,
                # 如果为False时， conn.close() 实际上被忽略，供下次使⽤，再线程关闭时，才会⾃动关
                # 闭链接。如果为True时， conn.close()则关闭链接，那么再次调⽤pool.connection时就会报
                # 错，因为已经真的关闭了连接（pool.steady_connection()可以获取⼀个新的链接）
                threadlocal=None,  # 本线程独享值得对象，⽤于保存链接对象，如果链接对象被重置
                host=self.server,
                user=self.user,
                password=self.password,
                database=self.database
            )
            self.connection = POOL.connection()
            self.cursor = self.connection.cursor()
            print('连接数据库')
        except pymssql.Error as e:
            print("Error connecting to SQL Server:", e)
    # 断开连接
    def close(self):
        if self.cursor:
            try:
                self.cursor.close()
            except pymssql.Error as e:
                print("Error closing cursor:", e)
        if self.connection:
            try:
                self.connection.close()
            except pymssql.Error as e:
                print("Error closing connection:", e)

# 学生DAO类
class StudentDAO:
    def __init__(self, connection):
        self.connection = connection

    def insert(self, student):
        query = "INSERT INTO student (sno, sname, sex, major_class, birth_date, contact_phone) VALUES (%s, %s, %s, %s, %s, %s)"
        values = (student.sno, student.sname, student.sex, student.major_class, student.birth_date, student.contact_phone)
        try:
            self.connection.cursor.execute(query, values)
            self.connection.connection.commit()
        except pymssql.Error as e:
            print("Error inserting student data:", e)

    def update(self, student):
        query = "UPDATE student SET sname = %s, sex = %s, major_class = %s, birth_date = %s, contact_phone = %s WHERE sno = %s"
        values = (student.sname, student.sex, student.major_class, student.birth_date, student.contact_phone, student.sno)
        try:
            self.connection.cursor.execute(query, values)
            self.connection.connection.commit()
        except pymssql.Error as e:
            print("Error updating student data:", e)

    def delete(self, sno):
        query = "DELETE FROM student WHERE sno = %s"
        try:
            self.connection.cursor.execute(query, (sno,))
            self.connection.connection.commit()
        except pymssql.Error as e:
            print("Error deleting student data:", e)

    def find_by_id(self, sno):
        query = "SELECT * FROM student WHERE sno = %s"
        try:
            self.connection.cursor.execute(query, (sno,))
            result = self.connection.cursor.fetchone()
            if result:
                return Student(result[0], result[1], result[2], result[3], result[4], result[5])
            return None
        except pymssql.Error as e:
            print("Error finding student by id:", e)

    def find_all(self):
        query = "SELECT * FROM student"
        try:
            self.connection.cursor.execute(query)
            results = self.connection.cursor.fetchall()
            students = []
            for result in results:
                students.append(Student(result[0], result[1], result[2], result[3], result[4], result[5]))
            return students
        except pymssql.Error as e:
            print("Error finding all students:", e)

# 课程DAO类
class CourseDAO:
    def __init__(self, connection):
        self.connection = connection

    def insert(self, course):
        query = "INSERT INTO courses (cno, cname, credit, hours, teacher) VALUES (%s, %s, %s, %s, %s)"
        values = (course.cno, course.cname, course.credit, course.hours, course.teacher)
        try:
            self.connection.cursor.execute(query, values)
            self.connection.connection.commit()
        except pymssql.Error as e:
            print("Error inserting course data:", e)

    def update(self, course):
        query = "UPDATE courses SET cname = %s, credit = %s, hours = %s, teacher = %s WHERE cno = %s"
        values = (course.cname, course.credit, course.hours, course.teacher, course.cno)
        try:
            self.connection.cursor.execute(query, values)
            self.connection.connection.commit()
        except pymssql.Error as e:
            print("Error updating course data:", e)

    def delete(self, cno):
        query = "DELETE FROM courses WHERE cno = %s"
        try:
            self.connection.cursor.execute(query, (cno,))
            self.connection.connection.commit()
        except pymssql.Error as e:
            print("Error deleting course data:", e)

    def find_by_id(self, cno):
        query = "SELECT * FROM courses WHERE cno = %s"
        try:
            self.connection.cursor.execute(query, (cno,))
            result = self.connection.cursor.fetchone()
            if result:
                return Course(result[0], result[1], result[2], result[3], result[4])
            return None
        except pymssql.Error as e:
            print("Error finding course by id:", e)

    def find_all(self):
        query = "SELECT * FROM courses"
        try:
            self.connection.cursor.execute(query)
            results = self.connection.cursor.fetchall()
            courses = []
            for result in results:
                courses.append(Course(result[0], result[1], result[2], result[3], result[4]))
            return courses
        except pymssql.Error as e:
            print("Error finding all courses:", e)

# 学生作业DAO类
class StudentHomeworkDAO:
    def __init__(self, connection):
        self.connection = connection

    def insert(self, studentHomework):
        query = "INSERT INTO student_homework (cno, sno, score1, score2, score3) VALUES (%s, %s, %s, %s, %s)"
        values = (studentHomework.cno, studentHomework.sno, studentHomework.score1, studentHomework.score2, studentHomework.score3)
        try:
            self.connection.cursor.execute(query, values)
            self.connection.connection.commit()
        except pymssql.Error as e:
            print("Error inserting student homework data:", e)

    def update(self, studentHomework):
        query = "UPDATE student_homework SET score1 = %s, score2 = %s, score3 = %s WHERE cno = %s AND sno = %s"
        values = (studentHomework.score1, studentHomework.score2, studentHomework.score3, studentHomework.cno, studentHomework.sno)
        try:
            self.connection.cursor.execute(query, values)
            self.connection.connection.commit()
        except pymssql.Error as e:
            print("Error updating student homework data:", e)

    def delete(self, sno, cno):
        query = "DELETE FROM student_homework WHERE sno = %s AND cno = %s"
        try:
            self.connection.cursor.execute(query, (sno, cno))
            self.connection.connection.commit()
        except pymssql.Error as e:
            print("Error deleting student homework data:", e)

    def find_by_id(self, sno, cno):
        query = "SELECT * FROM student_homework WHERE sno = %s AND cno = %s"
        try:
            self.connection.cursor.execute(query, (sno, cno))
            result = self.connection.cursor.fetchone()
            if result:
                return StudentHomework(result[0], result[1], result[2], result[3], result[4])
            return None
        except pymssql.Error as e:
            print("Error finding student homework by id:", e)

    def find_all(self):
        query = "SELECT * FROM student_homework"
        try:
            self.connection.cursor.execute(query)
            results = self.connection.cursor.fetchall()
            studentHomework = []
            for result in results:
                studentHomework.append(StudentHomework(result[0], result[1], result[2], result[3], result[4]))
            return studentHomework
        except pymssql.Error as e:
            print("Error finding all student homework:", e)

# DAO工厂类
class DAOFactory:
    server = 'localhost'
    user ='sa'
    password = '123456'
    database = 'education'
    def __init__(self, server, user, password, database):
        self.server = server
        self.user = user
        self.password = password
        self.database = database

    def getStudentDAO(self):
        connection = DAOConnection(self.server, self.user, self.password, self.database)
        connection.connect()
        return StudentDAO(connection)

    def getCourseDAO(self):
        connection = DAOConnection(self.server, self.user, self.password, self.database)
        connection.connect()
        return CourseDAO(connection)

    def getStudentHomeworkDAO(self):
        connection = DAOConnection(self.server, self.user, self.password, self.database)
        connection.connect()
        return StudentHomeworkDAO(connection)

server = 'localhost'
user ='sa'
password = '123456'
database = 'education'

# 使用DAO工厂获取学生DAO对象
factory = DAOFactory(server, user, password, database)
student_dao = factory.getStudentDAO()

# 插入一个学生记录
# new_student = Student('0600', '陈晨', '女', '计算机06', '1990-05-10', '13600001111')
# student_dao.insert(new_student)

# 更新一个学生记录
updated_student = student_dao.find_by_id('0600')
updated_student.sname = '陈思'
student_dao.update(updated_student)

# 删除一个学生记录
# student_dao.delete('0600')
#
# 获取所有学生记录
all_students = student_dao.find_all()
for student in all_students:
    print(student)
#
# 使用DAO工厂获取课程DAO对象
course_dao = factory.getCourseDAO()
#
# 插入一个课程记录
new_course = Course('K008', '人工智能基础', '3', '48', '李老师')
course_dao.insert(new_course)
#
# 更新一个课程记录
updated_course = course_dao.find_by_id('K001')
updated_course.credit = '3.5'
course_dao.update(updated_course)
#
# 删除一个课程记录
course_dao.delete('K008')
#
# 获取所有课程记录
all_courses = course_dao.find_all()
for course in all_courses:
    print(course)

# 使用DAO工厂获取学生作业DAO对象
student_homework_dao = factory.getStudentHomeworkDAO()

# 插入一个学生作业记录
new_homework = StudentHomework('K001', '0433', '65', '78', '80')
student_homework_dao.insert(new_homework)

# 更新一个学生作业记录
updated_homework = student_homework_dao.find_by_id('0433', 'K001')
updated_homework.score2 = '82'
student_homework_dao.update(updated_homework)

# 删除一个学生作业记录
student_homework_dao.delete('0433', 'K001')

# 获取所有学生作业记录
all_homework = student_homework_dao.find_all()
for homework in all_homework:
    print(homework)